/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package camada.bancodedados.daoimplemente.banco;

import camada.bancodedados.conexao.Conexao;
import camada.negocio.dao.banco.BancoDAO;
import eloma.classe.banco.Banco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Icaro
 */
public class BancoDAOImpl implements BancoDAO {

    private static Connection con;
    PreparedStatement stmt = null;
    ResultSet rs = null;

    @Override
    public void inserir(Banco banco) throws Exception {

        try {

            con = Conexao.getCon();
            con.setAutoCommit(false);

            final String SQL_SCRIPT = "insert into Banco (numero, nome) values (?,?)";

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setString(1, banco.getNumero());
            stmt.setString(1, banco.getNome());

            stmt.executeUpdate();

            con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }
    }

    @Override
    public List<Banco> buscaBancoLikeNome(String nomeBanco) throws Exception {

        try {
            final String SQL_SCRIPT = "select * from banco where nome  like '%" + nomeBanco + "%'";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            rs = stmt.executeQuery();

            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarMultiplosResultados(rs);
    }

    @Override

    public Banco buscaBancoId(int idBanco) throws Exception {

        try {

            final String SQL_SCRIPT = "select * from banco where idbanco = ?";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setInt(1, idBanco);
            rs = stmt.executeQuery();

            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarResultadoSimples(rs);
    }

    @Override
    public void atualizar(Banco banco) throws Exception {

        try {

            final String SQL_SCRIPT = "update banco set numero = ?,nome = ? where idBanco = ?";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setString(1, banco.getNumero());
            stmt.setString(2, banco.getNome());
            stmt.setInt(19, banco.getIdBanco());

            stmt.executeUpdate();

            con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }
    }

    @Override
    public void remover(Banco banco) throws Exception {

        try {

            final String SQL_SCRIPT = "delete from banco where idBanco = ?";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            stmt.setInt(1, banco.getIdBanco());
            stmt.executeUpdate();

            con.commit();

        } catch (SQLException e) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

    }

    @Override
    public List<Banco> buscaTodos() throws Exception {

        try {

            String SQL_SCRIPT = "Select * from banco order by idBanco";

            con = Conexao.getCon();
            con.setAutoCommit(false);

            stmt = con.prepareStatement(SQL_SCRIPT);
            rs = stmt.executeQuery();

            con.commit();

        } catch (SQLException sqle) {
            con.rollback();
        } catch (Exception e) {
            con.rollback();
        }

        return carregarMultiplosResultados(rs);

    }

    public List<Banco> carregarMultiplosResultados(ResultSet rs) throws SQLException {
        List<Banco> resultList = new ArrayList<Banco>();
        while (rs.next()) {
            Banco dto = new Banco();
            carregarVO(dto, rs);
            resultList.add(dto);
        }
        return resultList;
    }

    private Banco carregarResultadoSimples(ResultSet rs) throws SQLException {
        if (rs.next()) {
            Banco dto = new Banco();
            carregarVO(dto, rs);
            return dto;
        } else {
            return null;
        }
    }

    private void carregarVO(Banco dto, ResultSet rs) throws SQLException {
        dto.setIdBanco(rs.getInt("IdBanco"));
        dto.setNumero(rs.getString("numero"));
        dto.setNome(rs.getString("nome"));
    }

}
